{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " # An Example shows how to create dynamic jdbc connection string\n",
    " \n",
    " ##  The example uses PostgreSQL DB\n",
    " ### Run db\n",
    " docker run --rm  --name postgresDBTest -e POSTGRES_PASSWORD=dbUserPassword1 -e POSTGRES_USER=dbUser1 -p 5432:5432 -d postgres\n",
    " \n",
    " \n",
    " ### Optionally run pgadmin4\n",
    " docker run --rm  -p 80:80 --link postgresDBTest    -e 'PGADMIN_DEFAULT_EMAIL=user@domain.com'     -e 'PGADMIN_DEFAULT_PASSWORD=SuperSecret'     -d dpage/pgadmin4\n",
    " \n",
    " ## Put the follwoing files in /tmp folder\n",
    " \n",
    " **dbUser.txt** with the following content **dbUser1**\n",
    " \n",
    " **dbPasswordUser.txt** with the following content **dbUserPassword1**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Configure the beakerx.PROPERTY_NAME object using the groovy kernel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%groovy\n",
    "String user = new File('/tmp/dbUser.txt').text\n",
    "String password = new File('/tmp/dbPasswordUser.txt').text\n",
    "beakerx.dbpassword = password\n",
    "beakerx.dbuser = user\n",
    "\"done\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create dynamic connection string by {$beakerx.PROPERTY_NAME}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%defaultDatasource jdbc:postgresql://localhost:5432/postgres?user={$beakerx.dbuser}&password={$beakerx.dbpassword}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "CREATE TABLE color (\n",
    "   id serial PRIMARY KEY,\n",
    "   name VARCHAR (50) UNIQUE NOT NULL,\n",
    "   code VARCHAR (50) NOT NULL\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "INSERT INTO color (id, name, code) VALUES (1001,'AliceBlue','#F0F8FF');\n",
    "INSERT INTO color (id, name, code) VALUES (1002,'AntiqueWhite','#FAEBD7');\n",
    "INSERT INTO color (id, name, code) VALUES (1003,'Aqua','#00FFFF');\n",
    "INSERT INTO color (id, name, code) VALUES (1004,'Aquamarine','#7FFFD4');\n",
    "INSERT INTO color (id, name, code) VALUES (1005,'Azure','#F0FFFF');\n",
    "INSERT INTO color (id, name, code) VALUES (1006,'Beige','#F5F5DC');\n",
    "INSERT INTO color (id, name, code) VALUES (1007,'Bisque','#FFE4C4');\n",
    "INSERT INTO color (id, name, code) VALUES (1008,'Black','#000000');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "SELECT * FROM color WHERE name LIKE 'A%';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "drop table color"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "SQL",
   "language": "SQL",
   "name": "sql"
  },
  "language_info": {
   "codemirror_mode": "sql",
   "file_extension": ".sql",
   "mimetype": "",
   "name": "SQL",
   "nbconverter_exporter": "",
   "version": ""
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": false,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": false,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
